from django.shortcuts import render
from .models import BookInfo, HeroInfo
from django.db.models import F, Q, Sum, Avg

"""新增
save
create
"""

# book = BookInfo()
# book.btitle = '小三国'
# book.bpub_date = '2019-11-11'
# book.save()

# book = BookInfo(
#     btitle='大三国',
#     bpub_date='2020-1-1'
# )
# book.save()


# book = BookInfo.objects.create(
#     btitle='小红楼',
#     bpub_date='2020-1-2'
# )
#
# hero = HeroInfo.objects.create(
#     hname='超哥',
#     hbook=book,  # 直接给外键赋值应该赋模型对象
#     # hbook_id=book.id  # 给外键_id字段赋值应该模型对应模型的id
# )

"""
基本查询:
get  查询单一  如果查询不到会报错,如果查询出来的有多个也报错
all  无条件查询所有   返回QuerySet
count  查询数据数量
"""
# book = BookInfo.objects.get(id=1)
# book = BookInfo.objects.get(btitle='小三国')

# books = BookInfo.objects.all()

# count = BookInfo.objects.count()
#
# print(count)

"""
过滤查询:
get  查询单一  返回所查出来的模型对象
filter  根据条件查询满足条件的任意数量的数据  返回QuerySet
exclude  查询满足条件以外的所有数量  返回QuerySet
过滤条件语法:
模型属性字段__运算符
"""
# book = BookInfo.objects.filter(id=1)
# book = BookInfo.objects.filter(bread=10)
# book = BookInfo.objects.exclude(id=3)

# book = BookInfo.objects.filter(id__exact=1)

# book = BookInfo.objects.filter(btitle__contains='湖')
# book = BookInfo.objects.filter(btitle__endswith='部')
# book = BookInfo.objects.filter(btitle__isnull=False)
# book = BookInfo.objects.filter(id__in=[2, 4])
# book = BookInfo.objects.filter(id__gt=2)
# book = BookInfo.objects.filter(bpub_date__year='1980')
# book = BookInfo.objects.filter(bpub_date__gt='1990-1-1')
# book = BookInfo.objects.filter(bread__gt='20')
"""
F对象 可以实现两个字段之间比较 支持 + - * /
"""
# book = BookInfo.objects.filter(bread__gt=F('bcomment'))
# book = BookInfo.objects.filter(bread__gt=F('bcomment') * 2)
# id 大于2  bread大于10
# and &&  or ||  not !
# book = BookInfo.objects.filter(id__gt=2, bread__gt=10)  # 多个条件联合查询 条件都满足的才要

"""
Q对象  可以实现 and or not  基本查询
"""
# book = BookInfo.objects.filter(Q(id=2))
# book = BookInfo.objects.filter(Q(id__gt=2), Q(bread__gt=10))
# book = BookInfo.objects.filter(~Q(id=2))  # not  逻辑非 --->exclude
# book = BookInfo.objects.filter(Q(id__lt=2) | Q(bread__gt=30))
"""
聚合函数作用进行对某列数据进行运算 返回字典 字典key: 运算字段名__小写的聚合函数名
"""
# dict1 = BookInfo.objects.aggregate(Sum('bread'))
# dict2 = BookInfo.objects.aggregate(Avg('bread'))
# print(dict1)
# print(dict2)

"""
排序
order_by('排序字段名')  升序  从小到大
order_by('-排序字段名')  降序  从大到小

"""

# books = BookInfo.objects.order_by('id')
# books = BookInfo.objects.order_by('-bread')
# print(books)
"""
关联基本查询
BookInfo(一)
HeroInfo(多)
一查多:  一的这方模型对象.多的这方模型名小写_set.all()
一查多:  一的这方模型对象.多的这方模型名小写_set.filter(条件)
多查一:  多的一方模型对象.hbook
"""
# hero = HeroInfo.objects.get(hname='郭靖')
# book = hero.hbook
# print(book.bpub_date)

# book = BookInfo.objects.get(id=2)
# heros = book.heroinfo_set.filter(hname='王语嫣')
# print(heros)


"""
关联过滤查询:  在filter里面用另一方模型的字段来作为过滤条件
一查多: HeroInfo.objects.filter(hbook__一的这方模型字段作为过滤条件)
多查一: BookInfo.objects.filter(heroifno__hname='xx')
"""
# book = BookInfo.objects.filter(heroinfo__hname='王语嫣')
# print(book)
# hero = HeroInfo.objects.filter(hbook__btitle='天龙八部')
# print(hero)


# class One(Model):  # 一
#     name ='xxx'
#     # more_set
#
# class More(Model):  # 多
#     age = 'xx'
#     mone = 'xxx'  # 外键


# 一查多:  More.objects.filter(mone__name='xxx')
# 多查一: One.objects.filter(more__age='xx')


# 一查多:
# one =  One.objects.get(id=1)
# more = one.more_set.all()

# 多查一:
# more = More.objects.get(id=1)
# one = more.mone




"""删除 
模型对象.delete()  删除当前这一个模型对象所代表的数据
QuerySet.delete()  删除查询集中的所有模型对象数据
"""

# book = BookInfo.objects.get(id=8)

# count = book.delete()

# queryset = BookInfo.objects.filter(id__gte=5)
# count = queryset.delete()
# print(count)



"""
修改
把要修改的模型对象查询出来,然后给模型对象的属性重新赋值  save后才会修改表中数据
queryset.update()
"""

# book = BookInfo.objects.get(id=1)
# book.btitle = '射雕英雄传<<外传>>'
# book.save()

# qs = BookInfo.objects.filter(id__gt=1)
# qs.update(bread=100)




# '', [], {}, (), QueryDict(), QuerySet(), False, None, 0 这些放在if后面作为条件都是不成立
# qs = BookInfo.objects.all()
# print(qs)


# [book.id for book in BookInfo.objects.all()]
# [book.id for book in BookInfo.objects.all()]



# qs=BookInfo.objects.all()
# print(qs)
# count = qs.count()
# print(qs[:count - 1])
# [book.id for book in qs]
# [book.id for book in qs]


# book = BookInfo.books.all()
# print(book)






